package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Course;
import com.xxxy.jsq.model.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDao implements BaseDao<Course> {
    private C3P0Utils db = new C3P0Utils();

    public CourseDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Course model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_course(course_name,course_num,course_score,course_times,course_ltimes,course_stimes,course_flag) ");
        sqlStr.append("values(?,?,?,?,?,?,?) ");
        conn = db.getConnection();
        boolean flag = isSame(model, true);
        if (flag == false) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getCourseName());
                pstm.setString(2, model.getCourseNum());
                pstm.setDouble(3, model.getCourseScore());
                pstm.setInt(4, model.getCourseTimes());
                pstm.setInt(5, model.getCourseLtimes());
                pstm.setInt(6, model.getCourseStimes());
                pstm.setString(7, model.getCourseFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (num > 0) {
                return "Ok";
            } else {
                return "Err";
            }
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Course model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        boolean flag = isSame(model, false);
        if (flag == true) {
            return "Same";
        }
        sqlStr.append("update pa_course set ");
        if (model.getCourseName() != null && !model.getCourseName().equals("")) {
            sqlStr.append(" course_name='" + model.getCourseName() + "'");
        }
        if (model.getCourseNum() != null && !model.getCourseNum().equals("")) {
            sqlStr.append(" ,course_num='" + model.getCourseNum() + "'");
        }
        if (model.getCourseScore() != null) {
            sqlStr.append(" ,course_score=" + model.getCourseScore());
        }
        if (model.getCourseTimes() != null) {
            sqlStr.append(" ,course_times=" + model.getCourseTimes());
        }
        if (model.getCourseLtimes() != null) {
            sqlStr.append(" ,course_ltimes=" + model.getCourseLtimes());
        }
        if (model.getCourseStimes() != null) {
            sqlStr.append(" ,course_stimes=" + model.getCourseStimes());
        }
        if (model.getCourseFlag() != null && !model.getCourseFlag().equals("")) {
            sqlStr.append(" ,course_flag='" + model.getCourseFlag() + "'");
        }
        sqlStr.append(" where course_id=?");
        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getCourseId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(Course model) {
        return null;
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("delete from pa_course where course_id=? ");
        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }

    }

    @Override
    public List<Course> getAllData(Course model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Course> list = new ArrayList<Course>();
        sqlStr.append(" SELECT * from pa_course ");
        sqlStr.append("where 1=1 ");
        if (model.getCourseName() != null && !model.getCourseName().equals("")) {
            sqlStr.append("and course_name='" + model.getCourseName() + "' ");
        }
        if (model.getCourseNum() != null && !model.getCourseNum().equals("")) {
            sqlStr.append(" and course_num='" + model.getCourseNum() + "' ");
        }
        if (model.getCourseFlag() != null && !model.getCourseFlag().equals("")) {
            sqlStr.append(" and course_flag='" + model.getCourseFlag() + "' ");
        }
        sqlStr.append(" ORDER BY course_stmp desc,course_id asc,course_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Course md = new Course();
                md.setCourseId(rs.getLong("course_id"));
                md.setCourseName(rs.getString("course_name"));
                md.setCourseNum(rs.getString("course_num"));
                md.setCourseScore(rs.getDouble("course_score"));
                md.setCourseTimes(rs.getInt("course_times"));
                md.setCourseLtimes(rs.getInt("course_ltimes"));
                md.setCourseStimes(rs.getInt("course_stimes"));
                md.setCourseFlag(rs.getString("course_flag"));
                md.setCourseStmp(rs.getTimestamp("course_stmp"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Course findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_course");
        sqlStr.append("where 1=1 and course_id=? ");

        conn = db.getConnection();
        Course md = new Course();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                md.setCourseId(rs.getLong("course_id"));
                md.setCourseName(rs.getString("course_name"));
                md.setCourseNum(rs.getString("course_num"));
                md.setCourseScore(rs.getDouble("course_score"));
                md.setCourseTimes(rs.getInt("course_times"));
                md.setCourseLtimes(rs.getInt("course_ltimes"));
                md.setCourseStimes(rs.getInt("course_stimes"));
                md.setCourseFlag(rs.getString("course_flag"));
                md.setCourseStmp(rs.getTimestamp("course_stmp"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public Course find(Course model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_course ");
        sqlStr.append("where 1=1 ");
        if (model.getCourseName() != null && !model.getCourseName().equals("")) {
            sqlStr.append("and course_name='" + model.getCourseName() + "' ");
        }
        if (model.getCourseNum() != null && !model.getCourseNum().equals("")) {
            sqlStr.append(" and course_num='" + model.getCourseNum() + "' ");
        }
        if (model.getCourseFlag() != null && !model.getCourseFlag().equals("")) {
            sqlStr.append(" and course_flag='" + model.getCourseFlag() + "' ");
        }
        conn = db.getConnection();
        Course md = new Course();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                md.setCourseId(rs.getLong("course_id"));
                md.setCourseName(rs.getString("course_name"));
                md.setCourseNum(rs.getString("course_num"));
                md.setCourseScore(rs.getDouble("course_score"));
                md.setCourseTimes(rs.getInt("course_times"));
                md.setCourseLtimes(rs.getInt("course_ltimes"));
                md.setCourseStimes(rs.getInt("course_stimes"));
                md.setCourseFlag(rs.getString("course_flag"));
                md.setCourseStmp(rs.getTimestamp("course_stmp"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public boolean isSame(Course model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" select * from pa_course where course_name=? ");
        if (sflag == false) {
            sqlStr.append("and course_id!='" + model.getCourseId() + "' ");
        }
        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getCourseName());
            rs = pstm.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Course> getAllDataByPage(Course model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Course> list = new ArrayList<Course>();
        sqlStr.append(" SELECT * from pa_course ");
        sqlStr.append("where 1=1 ");
        if (model.getCourseName() != null && !model.getCourseName().equals("")) {
            sqlStr.append("and course_name like '%" + model.getCourseName() + "%' ");
        }
        if (model.getCourseNum() != null && !model.getCourseNum().equals("")) {
            sqlStr.append(" and course_num like '%" + model.getCourseNum() + "%' ");
        }
        if (model.getCourseFlag() != null && !model.getCourseFlag().equals("")) {
            sqlStr.append(" and course_flag='" + model.getCourseFlag() + "' ");
        }
        sqlStr.append(" ORDER BY course_stmp desc,course_id asc,course_name asc ");
        sqlStr.append(" limit ?,? ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Course md = new Course();
                md.setCourseId(rs.getLong("course_id"));
                md.setCourseName(rs.getString("course_name"));
                md.setCourseNum(rs.getString("course_num"));
                md.setCourseScore(rs.getDouble("course_score"));
                md.setCourseTimes(rs.getInt("course_times"));
                md.setCourseLtimes(rs.getInt("course_ltimes"));
                md.setCourseStimes(rs.getInt("course_stimes"));
                md.setCourseFlag(rs.getString("course_flag"));
                md.setCourseStmp(rs.getTimestamp("course_stmp"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Course model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select count(*) from pa_course d ");
        sqlStr.append("where 1=1 ");
        if (model.getCourseName() != null && !model.getCourseName().equals("")) {
            sqlStr.append("and d.course_name='" + model.getCourseName() + "' ");
        }
        if (model.getCourseNum() != null && !model.getCourseNum().equals("")) {
            sqlStr.append(" and d.course_num='" + model.getCourseNum() + "' ");
        }
        if (model.getCourseFlag() != null && !model.getCourseFlag().equals("")) {
            sqlStr.append(" and d.course_flag='" + model.getCourseFlag() + "' ");
        }
        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Course> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Course> list = new ArrayList<Course>();
        sqlStr.append("select * from pa_course where course_flag='0' ");
        sqlStr.append("order by course_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Course md = new Course();
                md.setCourseId(rs.getLong("course_id"));
                md.setCourseName(rs.getString("course_name"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}